package com.tpc.control.jpa;

import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import com.tpc.control.util.Formatting;

/**
 * A data access object (DAO) providing persistence and search support for
 * ReserveItem entities. Transaction control of the save(), update() and
 * delete() operations must be handled externally by senders of these methods or
 * must be manually added to each of these methods for data to be persisted to
 * the JPA datastore.
 * 
 * @see com.tpc.control.jpa.ReserveItem
 * @author MyEclipse Persistence Tools
 */

public class ReserveItemDAO implements IReserveItemDAO {
	// property constants
	public static final String MATERIAL_NO = "materialNo";
	public static final String MATERIAL_DESIGN = "materialDesign";
	public static final String DIVISION = "division";
	public static final String RESERVE_QTY = "reserveQty";
	public static final String UOM = "uom";
	public static final String STATUS = "status";
	public static final String CREATE_BY = "createBy";
	public static final String CHANGE_BY = "changeBy";
	public static final String CONFIRM_LAB_REMARK = "confirmLabRemark";
	public static final String SUMMARY_COSTING_REMARK = "summaryCostingRemark";
	public static final String LAB_CHANGE_BY = "labChangeBy";
	public static final String RECEIVE_RAW_MATERIAL_REMARK = "receiveRawMaterialRemark";
	public static final String RAW_CHANGE_BY = "rawChangeBy";
	public static final String GREIGE_FINISH_MACHINE = "greigeFinishMachine";
	public static final String GREIGE_FINISH_REMARK = "greigeFinishRemark";
	public static final String GREIGE_CHANGE_BY = "greigeChangeBy";
	public static final String DYEING_FINISH_MACHINE = "dyeingFinishMachine";
	public static final String DYEING_FINISH_REMARK = "dyeingFinishRemark";
	public static final String DYEING_CHANGE_BY = "dyeingChangeBy";
	public static final String RESERVE_NO = "reserveHeader.reserveNo";    //Customize

	private EntityManager getEntityManager() {
		return EntityManagerHelper.getEntityManager();
	}

	/**
	 * Perform an initial save of a previously unsaved ReserveItem entity. All
	 * subsequent persist actions of this entity should use the #update()
	 * method. This operation must be performed within the a database
	 * transaction context for the entity's data to be permanently saved to the
	 * persistence store, i.e., database. This method uses the
	 * {@link javax.persistence.EntityManager#persist(Object)
	 * EntityManager#persist} operation.
	 * 
	 * <pre>
	 * EntityManagerHelper.beginTransaction();
	 * ReserveItemDAO.save(entity);
	 * EntityManagerHelper.commit();
	 * </pre>
	 * 
	 * @param entity
	 *            ReserveItem entity to persist
	 * @throws RuntimeException
	 *             when the operation fails
	 */
	public void save(ReserveItem entity) {
		EntityManagerHelper
				.log("saving ReserveItem instance", Level.INFO, null);
		try {
			getEntityManager().persist(entity);
			EntityManagerHelper.log("save successful", Level.INFO, null);
		} catch (RuntimeException re) {
			EntityManagerHelper.log("save failed", Level.SEVERE, re);
			throw re;
		}
	}

	/**
	 * Delete a persistent ReserveItem entity. This operation must be performed
	 * within the a database transaction context for the entity's data to be
	 * permanently deleted from the persistence store, i.e., database. This
	 * method uses the {@link javax.persistence.EntityManager#remove(Object)
	 * EntityManager#delete} operation.
	 * 
	 * <pre>
	 * EntityManagerHelper.beginTransaction();
	 * ReserveItemDAO.delete(entity);
	 * EntityManagerHelper.commit();
	 * entity = null;
	 * </pre>
	 * 
	 * @param entity
	 *            ReserveItem entity to delete
	 * @throws RuntimeException
	 *             when the operation fails
	 */
	public void delete(ReserveItem entity) {
		EntityManagerHelper.log("deleting ReserveItem instance", Level.INFO,
				null);
		try {
			entity = getEntityManager().getReference(ReserveItem.class,
					entity.getId());
			getEntityManager().remove(entity);
			EntityManagerHelper.log("delete successful", Level.INFO, null);
		} catch (RuntimeException re) {
			EntityManagerHelper.log("delete failed", Level.SEVERE, re);
			throw re;
		}
	}

	/**
	 * Persist a previously saved ReserveItem entity and return it or a copy of
	 * it to the sender. A copy of the ReserveItem entity parameter is returned
	 * when the JPA persistence mechanism has not previously been tracking the
	 * updated entity. This operation must be performed within the a database
	 * transaction context for the entity's data to be permanently saved to the
	 * persistence store, i.e., database. This method uses the
	 * {@link javax.persistence.EntityManager#merge(Object) EntityManager#merge}
	 * operation.
	 * 
	 * <pre>
	 * EntityManagerHelper.beginTransaction();
	 * entity = ReserveItemDAO.update(entity);
	 * EntityManagerHelper.commit();
	 * </pre>
	 * 
	 * @param entity
	 *            ReserveItem entity to update
	 * @return ReserveItem the persisted ReserveItem entity instance, may not be
	 *         the same
	 * @throws RuntimeException
	 *             if the operation fails
	 */
	public ReserveItem update(ReserveItem entity) {
		EntityManagerHelper.log("updating ReserveItem instance", Level.INFO,
				null);
		try {
			ReserveItem result = getEntityManager().merge(entity);
			EntityManagerHelper.log("update successful", Level.INFO, null);
			return result;
		} catch (RuntimeException re) {
			EntityManagerHelper.log("update failed", Level.SEVERE, re);
			throw re;
		}
	}

	public ReserveItem findById(ReserveItemId id) {
		EntityManagerHelper.log("finding ReserveItem instance with id: " + id,
				Level.INFO, null);
		try {
			ReserveItem instance = getEntityManager().find(ReserveItem.class,
					id);
			return instance;
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find failed", Level.SEVERE, re);
			throw re;
		}
	}

	/**
	 * Find all ReserveItem entities with a specific property value.
	 * 
	 * @param propertyName
	 *            the name of the ReserveItem property to query
	 * @param value
	 *            the property value to match
	 * @return List<ReserveItem> found by query
	 */
	@SuppressWarnings("unchecked")
	public List<ReserveItem> findByProperty(String propertyName,
			final Object value) {
		EntityManagerHelper.log("finding ReserveItem instance with property: "
				+ propertyName + ", value: " + value, Level.INFO, null);
		try {
			final String queryString = "select model from ReserveItem model where model."
					+ propertyName + "= :propertyValue";
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyValue", value);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<ReserveItem> findByMaterialNo(Object materialNo) {
		return findByProperty(MATERIAL_NO, materialNo);
	}

	public List<ReserveItem> findByMaterialDesign(Object materialDesign) {
		return findByProperty(MATERIAL_DESIGN, materialDesign);
	}

	public List<ReserveItem> findByDivision(Object division) {
		return findByProperty(DIVISION, division);
	}

	public List<ReserveItem> findByReserveQty(Object reserveQty) {
		return findByProperty(RESERVE_QTY, reserveQty);
	}

	public List<ReserveItem> findByUom(Object uom) {
		return findByProperty(UOM, uom);
	}

	public List<ReserveItem> findByStatus(Object status) {
		return findByProperty(STATUS, status);
	}

	public List<ReserveItem> findByCreateBy(Object createBy) {
		return findByProperty(CREATE_BY, createBy);
	}

	public List<ReserveItem> findByChangeBy(Object changeBy) {
		return findByProperty(CHANGE_BY, changeBy);
	}

	public List<ReserveItem> findByConfirmLabRemark(Object confirmLabRemark) {
		return findByProperty(CONFIRM_LAB_REMARK, confirmLabRemark);
	}

	public List<ReserveItem> findBySummaryCostingRemark(
			Object summaryCostingRemark) {
		return findByProperty(SUMMARY_COSTING_REMARK, summaryCostingRemark);
	}

	public List<ReserveItem> findByLabChangeBy(Object labChangeBy) {
		return findByProperty(LAB_CHANGE_BY, labChangeBy);
	}

	public List<ReserveItem> findByReceiveRawMaterialRemark(
			Object receiveRawMaterialRemark) {
		return findByProperty(RECEIVE_RAW_MATERIAL_REMARK,
				receiveRawMaterialRemark);
	}

	public List<ReserveItem> findByRawChangeBy(Object rawChangeBy) {
		return findByProperty(RAW_CHANGE_BY, rawChangeBy);
	}

	public List<ReserveItem> findByGreigeFinishMachine(
			Object greigeFinishMachine) {
		return findByProperty(GREIGE_FINISH_MACHINE, greigeFinishMachine);
	}

	public List<ReserveItem> findByGreigeFinishRemark(Object greigeFinishRemark) {
		return findByProperty(GREIGE_FINISH_REMARK, greigeFinishRemark);
	}

	public List<ReserveItem> findByGreigeChangeBy(Object greigeChangeBy) {
		return findByProperty(GREIGE_CHANGE_BY, greigeChangeBy);
	}

	public List<ReserveItem> findByDyeingFinishMachine(
			Object dyeingFinishMachine) {
		return findByProperty(DYEING_FINISH_MACHINE, dyeingFinishMachine);
	}

	public List<ReserveItem> findByDyeingFinishRemark(Object dyeingFinishRemark) {
		return findByProperty(DYEING_FINISH_REMARK, dyeingFinishRemark);
	}

	public List<ReserveItem> findByDyeingChangeBy(Object dyeingChangeBy) {
		return findByProperty(DYEING_CHANGE_BY, dyeingChangeBy);
	}

	/**
	 * Find all ReserveItem entities.
	 * 
	 * @return List<ReserveItem> all ReserveItem entities
	 */
	@SuppressWarnings("unchecked")
	public List<ReserveItem> findAll() {
		EntityManagerHelper.log("finding all ReserveItem instances",
				Level.INFO, null);
		try {
			final String queryString = "select model from ReserveItem model";
			Query query = getEntityManager().createQuery(queryString);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find all failed", Level.SEVERE, re);
			throw re;
		}
	}
	
	/* Customize */
	public List<ReserveItem> findByHeader(Object reserveNo) {
		return findByProperty(RESERVE_NO, reserveNo);
	}

	public List<ReserveItem> findReport(Object item,Object divi,
			Object date_low,Object date_high,Object cust_no,Object reserveDate_low,Object reserveDate_high) {
		EntityManagerHelper.log("finding Report with Attribute in ReserveItem ", Level.INFO, null);
		try {
			boolean condition = true;
			
			String num = item.toString();
			String div = divi.toString();
			Date []date = Formatting.generateFromToDate(date_low, date_high);
			Date []reserve_date = Formatting.generateFromToDate(reserveDate_low, reserveDate_high);
			String queryString = "select model from ReserveItem model ";
			System.out.println(reserveDate_low);
			
			Date tempReserve = new Date();
			Date tempReserve2 = new Date();
			//tempReserve.setDate(reserve_date[0].getDate());
			tempReserve.setDate(reserve_date[0].getDate());
			tempReserve.setMonth(reserve_date[0].getMonth());
			tempReserve.setYear(reserve_date[0].getYear());
			tempReserve.setHours(23);
			tempReserve.setMinutes(59);
			tempReserve.setSeconds(59);
			
			tempReserve2.setDate(reserve_date[1].getDate());
			tempReserve2.setMonth(reserve_date[1].getMonth());
			tempReserve2.setYear(reserve_date[1].getYear());
			tempReserve2.setHours(23);
			tempReserve2.setMinutes(59);
			tempReserve2.setSeconds(59);
			
			if(!item.equals(""))
			{
				if(condition)
				{
				queryString += "where ";
				condition = false;
				}
				else {
					queryString += "and ";
				}
				queryString += "model.id.reserveNo = :propertyValue1 ";
				//queryString += "model.reserveHeader.reserveNo = :propertyValue1 ";
			}
			if(!div.equals(""))
			{
				if(condition)
				{
				queryString += "where ";
				condition = false;
				}
				else {
					queryString += "and ";
				}
				queryString += "model.division = :propertyValue2 ";
			}
			if (date_low!=null || date_high!=null) {
				if (condition) {
					queryString += " where ";
					condition = false;
				} else {
					queryString += " and ";
				}
				if(date_low!=null && date_high == null)
				{
					queryString += "model.deliveryDate = :propertyValue3 ";	
				}else{
				queryString += " (model.deliveryDate " + " between :propertyValue3 "
				+ " and :propertyValue4 ) ";
				}
			}
			if (cust_no!=""){
				if(condition)
				{
				queryString += "where ";
				condition = false;
				}
				else {
					queryString += "and ";
				}
				queryString += " model.reserveHeader.customer = :propertyValue5 ";
			}
			if (reserveDate_low!=null || reserveDate_high!=null){
				if (condition) {
					queryString += " where ";
					condition = false;
				} else {
					queryString += " and ";
				}
				if(reserveDate_low!=null && reserveDate_high == null){
					//queryString += " model.reserveHeader.createDate >= ':propertyValue6 00:00:00 AM' and model.reserveHeader.createDate <= ':propertyValue6 11:59:59 PM' ";
					queryString += " (model.reserveHeader.createDate = :propertyValue6 "
					+ " and :propertyValue7 ) ";
				}else{
					queryString += " (model.reserveHeader.createDate " + " between :propertyValue6 "
					+ " and :propertyValue7 ) ";
					/*queryString += " (model.reserveHeader.createDate >= :propertyValue6 and model.reserveHeader.createDate <= :propertyValue7 11:59:59 PM) "; */
					}
			}

			System.out.println("queryString: "+queryString);
			Query query = getEntityManager().createQuery(queryString);
			
/*			if (date_low!=null && date_high == null)
			{
				query.setParameter("propertyValue3", date_low);
				query.setParameter("propertyValue4", null);
			}*/
			if(!item.equals(""))
			{
				query.setParameter("propertyValue1", item);
			}
			if(!div.equals(""))
			{
				query.setParameter("propertyValue2", divi);
			}
			if(date_low!=null && date_high == null)
			{
				query.setParameter("propertyValue3", date_low);	
			}
			if (date_low!=null && date_high!=null) {
				query.setParameter("propertyValue3", date_low);
				query.setParameter("propertyValue4", date_high);
			}
			if(!cust_no.equals(""))
			{
				query.setParameter("propertyValue5", cust_no);
			}
			if(reserveDate_low!=null && reserveDate_high == null){
				query.setParameter("propertyValue6", reserveDate_low);
				query.setParameter("propertyValue7", tempReserve);
			}
			if(reserveDate_low!=null && reserveDate_high != null){
				query.setParameter("propertyValue6", reserveDate_low);
				query.setParameter("propertyValue7", tempReserve2);
			}
			//query.setParameter("propertyValue1", item);
			//query.setParameter("propertyValue2", divi);
		
			
			
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}

	}
	public List<ReserveItem> findByCustnoDeliverydateandDivision(Object customer_no,Object division,Object delivery_date_low,Object delivery_date_high) {
		EntityManagerHelper.log("finding ReserveItem instance with property: reserve_no ,"
				 , Level.INFO, null);
		try {
			String queryString = "select model from ReserveItem model ";
			
			boolean condition = true;
			
			Date []adate = Formatting.generateFromToDate(delivery_date_low, delivery_date_high);
			
			if (customer_no!=""){
				if(condition)
				{
				queryString += " where ";
				condition = false;
				}
				else {
					queryString += " and ";
				}
				queryString += " model.reserveHeader.customer = :propertyValue1 ";
			}
			
			if(!division.equals(""))
			{
				if(condition)
				{
					queryString += " where ";
					condition = false;
				}
				else {
					queryString += " and ";
				}
				queryString += " model.division = :propertyValue2 ";
			}
			if (delivery_date_low!=null || delivery_date_high!=null) {
				if (condition) {
					queryString += " where ";
					condition = false;
				} else {
					queryString += " and ";
				}
				if(delivery_date_low!=null && delivery_date_high == null)
				{
					queryString += " model.deliveryDate = :propertyValue3 ";	
				}else{
				queryString += " (model.deliveryDate " + " between :propertyValue3 "
				+ " and :propertyValue4 ) ";
				}
			}
			
			Query query = getEntityManager().createQuery(queryString);
			
			if(!customer_no.equals(""))
			{
				System.out.println(customer_no);
				query.setParameter("propertyValue1", customer_no);
			}
			if(!division.equals(""))
			{
				query.setParameter("propertyValue2", division);
			}
			if(delivery_date_low!=null && delivery_date_high == null)
			{
				query.setParameter("propertyValue3", adate[0]);	
			}
			if (delivery_date_low!=null && delivery_date_high!=null) {
				query.setParameter("propertyValue3", adate[0]);
				query.setParameter("propertyValue4", adate[1]);
			}
			
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public Double findSumQuantity(Object propertyName1) {
		EntityManagerHelper.log("finding Sum Quantity ReserveItem", Level.INFO, null);
		try {
			final String queryString = "select SUM(model.reserveQty) from ReserveItem model where model.division = :propertyName1";
					
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyName1", propertyName1);

			return (Double) query.getSingleResult();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
	public List<String> findDistinctDivision(Object customer_no,Object division,Object delivery_date_low,Object delivery_date_high) {
		EntityManagerHelper.log("finding ReserveItem instance with property: reserve_no ,"
				 , Level.INFO, null);
		try {
			String queryString = "select DISTINCT model.division from ReserveItem model ";
			
			boolean condition = true;
			
			Date []adate = Formatting.generateFromToDate(delivery_date_low, delivery_date_high);
			
			if (customer_no!=""){
				if(condition)
				{
				queryString += " where ";
				condition = false;
				}
				else {
					queryString += " and ";
				}
				queryString += " model.reserveHeader.customer = :propertyValue1 ";
			}
			
			if(!division.equals(""))
			{
				if(condition)
				{
					queryString += " where ";
					condition = false;
				}
				else {
					queryString += " and ";
				}
				queryString += " model.division = :propertyValue2 ";
			}
			if (delivery_date_low!=null || delivery_date_high!=null) {
				if (condition) {
					queryString += " where ";
					condition = false;
				} else {
					queryString += " and ";
				}
				if(delivery_date_low!=null && delivery_date_high == null)
				{
					queryString += " model.deliveryDate = :propertyValue3 ";	
				}else{
				queryString += " (model.deliveryDate " + " between :propertyValue3 "
				+ " and :propertyValue4 ) ";
				}
			}
			
			Query query = getEntityManager().createQuery(queryString);
			
			if(!customer_no.equals(""))
			{
				System.out.println(customer_no);
				query.setParameter("propertyValue1", customer_no);
			}
			if(!division.equals(""))
			{
				query.setParameter("propertyValue2", division);
			}
			if(delivery_date_low!=null && delivery_date_high == null)
			{
				query.setParameter("propertyValue3", adate[0]);	
			}
			if (delivery_date_low!=null && delivery_date_high!=null) {
				query.setParameter("propertyValue3", adate[0]);
				query.setParameter("propertyValue4", adate[1]);
			}
			
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
	
	public Double findQuantity(Object propertyName1,Object propertyName2) {
		EntityManagerHelper.log("finding all ReserveItem instances",
				Level.INFO, null);
		try {
			final String queryString = "select model.reserveQty from ReserveItem model.id.reserveNo = :propertyName1 and model.id.reserveLine = :propertyName2";
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyName1", propertyName1);
			query.setParameter("propertyName2", propertyName2);
			return (Double) query.getSingleResult();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find all failed", Level.SEVERE, re);
			throw re;
		}
	}
}


	
